﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Reflection;
using System.Text;
using System.Data;
using System.Data.Common;
using Water.Architecture2.Model;
using Water.Architecture2.IDAL;
using Water.Architecture2.EntityXML;
using Water.DA;
using Water.Resources.Globalize;
using Water.Web.Prompt;
using Water.Web.Controls;

namespace Water.Architecture2.DAL
{
    public abstract class BaseDAL<T> : IBaseDAL<T> where T : BaseEntity, new()
    {
        #region === private
        private static T entity = new T();

        //native table or view or sql alias
        private const string ALIAS = "T";

        private string ConnectionStringName
        {
            get { return entity.ConnectionStringName; }
        }

        public virtual Database GetDataBase()
        {
            Database db;
            if (string.IsNullOrEmpty(ConnectionStringName))
                db= DatabaseFactory.CreateDatabase();
            else
                db = DatabaseFactory.CreateDatabase(ConnectionStringName);
            return db;
        }

        #endregion

        #region === 辅助方法
        private string GetColumnName(string propertyName) 
        {
            string colName = string.Empty;
            if (entity.PropertieColumnsMap.ContainsKey(propertyName.ToUpper()))
                colName = entity.PropertieColumnsMap[propertyName.ToUpper()];
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionEntityAssemblyNotFound,entity.GetType().ToString(),propertyName));
            return colName;
        }

        private string CreateAnyWhereClause(WhereFields w_fields, Database db, bool isQueryWhere) //isQueryWhere是否查询where条件
        {
            string where_clause = "";
            if (null != w_fields &&  w_fields.Count != 0)
            {
                StringBuilder sb_where_clause = new StringBuilder();
                foreach (WhereField wf in w_fields)
                {
                    string name = wf.Name.ToUpper();
                    //string colName = (isQueryWhere && entity.Subquery) ? name : this.GetColumnName(name); 2010-03-28
                    string colName = isQueryWhere ? name : this.GetColumnName(name);
                    object value = wf.Value;
                    if (wf.IgnoreCase)
                    {
                        colName = "upper(" + colName + ")";
                    }

                    if (wf.IsOperator)
                    {
                        string strOperator=db.ToDbOpertrator(wf.Operator);
                        string dbParameterName = db.CreateParameterName(name);
                        if (OperatorType.In == wf.Operator || OperatorType.NotIn == wf.Operator)
                            sb_where_clause.Append(string.Format(" {0} {1} ({2}) AND", colName, strOperator, dbParameterName));
                        else if (OperatorType.IsNull == wf.Operator || OperatorType.IsNotNull == wf.Operator)
                            sb_where_clause.Append(string.Format(" {0} {1} AND", colName, strOperator, dbParameterName));
                        else
                            sb_where_clause.Append(string.Format(" {0} {1} {2} AND", colName, strOperator, dbParameterName));
                    }
                    else
                        sb_where_clause.Append(string.Format(" {0} AND", wf.WhereClause));

                }

                where_clause = sb_where_clause.ToString();
                where_clause = where_clause.Substring(0, where_clause.Length - 3);
                return where_clause;
            }
            return where_clause;
        }

        private string CreateAnyWhereClause(WhereFields w_fields, Database db)
        {
            return CreateAnyWhereClause(w_fields, db, false);
        }

        private static void CreateAnyDbParameter(WhereFields w_fields, Database db, DbCommand comm)
        {
            if (null != w_fields && w_fields.Count != 0)
            {
                foreach (WhereField wf in w_fields)
                {
                    string k = ((string)wf.Name).ToUpper();
                    Type t = entity.PropertiesType[k];
                    //Type t = (Type)((entity).FieldsType[k]);
                    t = (t == null) ? typeof(System.String) : t;

                    object value = wf.Value;
                    if (wf.IgnoreCase)
                    {
                        value = value.ToString().ToUpper();
                    }

                    db.AddInParameter(comm, (string)wf.Name, db.GetDbType(t), value);
                }
            }
        }
        /// <summary>
        /// 创建where 子句
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="db"></param>
        /// <param name="isQueryWhere">是否查询where条件</param>
        /// <returns></returns>
        private string CreateEqualsWhereClause(Hashtable fields, Database db, bool isQueryWhere)
        {
            string where_clause = "";
            if (fields.Count != 0)
            {
                StringBuilder sb_where_clause = new StringBuilder();
                foreach (DictionaryEntry dic in fields)
                {
                    string colName = (isQueryWhere && entity.Subquery)?(string)(dic.Key):this.GetColumnName((string)(dic.Key));
                    sb_where_clause.Append(string.Format(" {0} = {1} AND", colName, db.CreateParameterName(dic.Key.ToString())));
                }

                where_clause = sb_where_clause.ToString();
                where_clause = where_clause.Substring(0, where_clause.Length - 3);
                return where_clause;
            }
            return where_clause;
        }

        private string CreateEqualsWhereClause(Hashtable fields, Database db)
        {
            return CreateEqualsWhereClause(fields,db,false);
        }
        /// <summary>
        /// 创建参数，并添加到Database的DbCommand上
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="db"></param>
        /// <param name="comm"></param>
        private static void CreateEqualsDbParameter(System.Collections.Hashtable fields, Database db, DbCommand comm)
        {
            if (fields.Count != 0)
            {
                foreach (DictionaryEntry dic in fields)
                {
                    string k = ((string)dic.Key).ToUpper();
                    if (k.EndsWith("_"))
                        k=k.Substring(0,k.Length-1);
                    Type t = entity.PropertiesType[k];
                    //Type t = (Type)((entity).FieldsType[k]);
                    t = (t == null) ? typeof(System.String) : t;
                    db.AddInParameter(comm, (string)dic.Key, db.GetDbType(t), dic.Value);
                }
            }
        }
        #endregion

        #region IBaseDAL<T> 成员

        public virtual bool IsExist(WhereFields whereFields)
        {
            Database db = GetDataBase();

            string where_clause = CreateAnyWhereClause(whereFields, db,true);
            string sql = "";
            if (where_clause == "")
                sql = string.Format("SELECT COUNT(*) FROM ({0}) Y",db.GetBaseQuerySql(entity));
            else
                sql = string.Format("SELECT COUNT(*) FROM ({0} WHERE {1}) Y", db.GetBaseQuerySql(entity), where_clause);

            DbCommand comm = db.CreateSqlCommand(sql);

            CreateAnyDbParameter(whereFields, db, comm);

            return (int)db.ExecuteScalar(comm) > 0;
        }

        public virtual bool IsExist(System.Collections.Hashtable fields)
        {
            Database db = GetDataBase();

            string where_clause = CreateEqualsWhereClause(fields, db,true);
            string sql = "";
            if (where_clause == "")
                sql = string.Format("SELECT COUNT(*) FROM ({0}) Y", db.GetBaseQuerySql(entity));
            else
                sql = string.Format("SELECT COUNT(*) FROM ({0} WHERE {1}) Y", db.GetBaseQuerySql(entity), where_clause);

            DbCommand comm = db.CreateSqlCommand(sql);

            CreateEqualsDbParameter(fields, db, comm);

            return (int)db.ExecuteScalar(comm) > 0;
        }

        public virtual bool IsExist(string fieldName, object fieldValue)
        {
            Hashtable ht_fields = new Hashtable();
            ht_fields.Add(fieldName, fieldValue);
            return IsExist(ht_fields);
        }

        public virtual bool IsExist(int id)
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "IsExist(int id)"));
            }
            else
            {
                Hashtable ht_fields = new Hashtable();
                ht_fields.Add(entity.PrimaryKeys.ToUpper(), id);
                return IsExist(ht_fields);
            }
        }

        public virtual int GetMaxId()
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "GetMaxID"));
            }
            else
            {
                Database db = GetDataBase();
                string sql = string.Format("SELECT MAX({0}) FROM {1}", entity.PrimaryKeys, entity.Table);
                return (int)(db.ExecuteScalar(CommandType.Text, sql));
            }
        }

        public virtual int GetCount()
        {
            Database db = GetDataBase();
            string sql = string.Format("SELECT COUNT(*) FROM ({0}) Y", db.GetBaseQuerySql(entity));
            object obj = db.ExecuteScalar(CommandType.Text, sql);
            int count;
            if (int.TryParse(obj.ToString(), out count))
                return count;
            return -1;
        }

        public virtual int GetCount(Hashtable fields)
        {
            Database db = GetDataBase();

            string where_clause = CreateEqualsWhereClause(fields, db,true);
            string sql = "";
            if (where_clause == "")
                sql = string.Format("SELECT COUNT(*) FROM ({0}) Y", db.GetBaseQuerySql(entity));
            else
                sql = string.Format("SELECT COUNT(*) FROM ({0} WHERE {1}) Y", db.GetBaseQuerySql(entity), where_clause);

            DbCommand comm = db.CreateSqlCommand(sql);

            CreateEqualsDbParameter(fields, db, comm);

            //return (int)db.ExecuteScalar(comm);
            object obj = db.ExecuteScalar(comm);
            int count;
            if (int.TryParse(obj.ToString(), out count))
                return count;
            return -1;
        }

        public virtual int GetCount(WhereFields whereFields)
        {
            Database db = GetDataBase();

            string where_clause = CreateAnyWhereClause(whereFields, db,true);
            string sql = "";
            if (where_clause == "")
                sql = string.Format("SELECT COUNT(*) FROM ({0}) Y", db.GetBaseQuerySql(entity));
            else
                sql = string.Format("SELECT COUNT(*) FROM ({0} WHERE {1}) Y", db.GetBaseQuerySql(entity), where_clause);

            DbCommand comm = db.CreateSqlCommand(sql);

            CreateAnyDbParameter(whereFields, db, comm);

            //return (int)db.ExecuteScalar(comm);
            object obj = db.ExecuteScalar(comm);
            int count;
            if (int.TryParse(obj.ToString(), out count))
                return count;
            return -1;
        }

        public virtual int GetMatrixCount(WhereFields whereFields, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, DataView matrixColumns)
        {
            string sql = "";
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
        
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db, true);
                query_sql = query_sql + " WHERE " + where_clause;
            }
            string matrix_sql = db.CreateMatrixSql(query_sql, matrixColumnField, groupField, valueField, summaryType,matrixColumns);

            sql = string.Format("SELECT COUNT(*) FROM ({0}) Y", matrix_sql);
            
            DbCommand comm = db.CreateSqlCommand(sql);

            CreateAnyDbParameter(whereFields, db, comm);

            object obj = db.ExecuteScalar(comm);
            int count;
            if (int.TryParse(obj.ToString(), out count))
                return count;
            return -1;
        }
        

        public virtual int Insert(System.Collections.Hashtable fields, DbTransaction trans)
        {
            if (!entity.QueryOnly)
            {
                Database db = GetDataBase();

                string sql = "";
                string s_fields = "";
                string s_values = "";
                int result = 0;
                if (fields.Count != 0)
                {
                    StringBuilder sb_fields = new StringBuilder();
                    StringBuilder sb_values = new StringBuilder();
                    if (entity.AutoGenerators.Count != 0)
                    {
                        foreach (KeyValuePair<string,string> kv in entity.AutoGenerators)
                        {
                            sb_fields.Append(string.Format("{0},", kv.Key));
                            sb_values.Append(string.Format("{0},", kv.Value+".nextval"));
                            
                        }
                    }
                    foreach (DictionaryEntry dic in fields)
                    {
                        string _key = dic.Key.ToString().ToUpper();
                        string _key_column = this.GetColumnName(_key);
                        if (!entity.InsertIgnoreFields.Contains(_key_column)) //除去插入忽略字段
                        {
                            sb_fields.Append(string.Format("{0},", _key_column));
                            sb_values.Append(string.Format("{0},", db.CreateParameterName(_key)));
                        }  
                    }
                    s_fields = sb_fields.ToString(); s_fields = s_fields.Substring(0, s_fields.Length - 1);
                    s_values = sb_values.ToString(); s_values = s_values.Substring(0, s_values.Length - 1);
                    sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", entity.Table, s_fields, s_values);
                    DbCommand comm = db.CreateSqlCommand(sql);

                    //参数
                    foreach (DictionaryEntry dic in fields)
                    {
                        string k = ((string)dic.Key).ToUpper();
                        string k_col = this.GetColumnName(k);
                        if (!entity.InsertIgnoreFields.Contains(k_col)) //除去插入忽略字段
                        {
                            //Type t = (Type)((entity).FieldsType[k]);
                            Type t = entity.PropertiesType[k];
                            t = (t == null) ? typeof(System.String) : t;
                            db.AddInParameter(comm, (string)dic.Key, db.GetDbType(t), dic.Value);
                        }
                    }


                    if (trans == null)
                        result = db.ExecuteNonQuery(comm);
                    else
                        result = db.ExecuteNonQuery(comm, trans);
                }
                return result;
            }
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod,"Insert"));

        }

        public virtual int Insert(System.Collections.Hashtable fields)
        {
            return Insert(fields, null);
        }

        public virtual int Insert(T obj, DbTransaction trans)
        {
            Hashtable values =obj.PropertiesValue;// obj.FieldsValue;
            return Insert(values, trans);
        }

        public virtual int Insert(T obj)
        {
            return Insert(obj, null);
        }



        public virtual int Delete(WhereFields whereFields, DbTransaction trans)
        {
            if (!entity.QueryOnly)
            {
                Database db = GetDataBase();

                string sql = "";
                int result = 0;
                string where_clause = "";
                if (null != whereFields && whereFields.Count != 0)
                {
                    where_clause = CreateAnyWhereClause(whereFields, db);
                    sql = string.Format("DELETE {0} WHERE {1}", entity.Table, where_clause);
                    DbCommand comm = db.CreateSqlCommand(sql);

                    CreateAnyDbParameter(whereFields, db, comm);

                    if (trans == null)
                        result = db.ExecuteNonQuery(comm);
                    else
                        result = db.ExecuteNonQuery(comm, trans);
                }
                return result;
            }
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod,"Delete"));
        }

        public virtual int Delete(WhereFields whereFields)
        {
            return Delete(whereFields, null);
        }

        public virtual int Delete(Hashtable fields, DbTransaction trans)
        {
            if (!entity.QueryOnly)
            {
                Database db = GetDataBase();

                string sql = "";
                int result = 0;
                string where_clause = "";
                if (null != fields && fields.Count != 0)
                {
                    StringBuilder sb_where_clause = new StringBuilder();
                    foreach (DictionaryEntry dic in fields)
                    {
                        string _key = dic.Key.ToString().ToUpper();
                        sb_where_clause.Append(string.Format(" {0}={1} AND", this.GetColumnName(_key), db.CreateParameterName(_key)));
                    }
                    where_clause = sb_where_clause.ToString();
                    where_clause = where_clause.Substring(0, where_clause.Length - 3);
                    sql = string.Format("DELETE {0} WHERE {1}", entity.Table, where_clause);
                    DbCommand comm = db.CreateSqlCommand(sql);

                    CreateEqualsDbParameter(fields, db, comm);

                    if (trans == null)
                        result = db.ExecuteNonQuery(comm);
                    else
                        result = db.ExecuteNonQuery(comm, trans);
                }
                return result;
            }
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "Delete"));
        }

        public virtual int Delete(Hashtable fields)
        {
            return Delete(fields, null);
        }

        public virtual int Delete(int id)
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "Delete"));
            }
            else
            {
                Hashtable ht_fields = new Hashtable();
                ht_fields.Add(entity.PrimaryKeys.ToUpper(), id);
                return Delete(ht_fields);
            }
        }

        public virtual int Delete(T obj, DbTransaction trans)
        {
            Hashtable values = obj.PropertiesValue;//obj.FieldsValue;
            return Delete(values, trans);
        }

        public virtual int Delete(T obj)
        {
            return Delete(obj, null);
        }



        public virtual int Update(Hashtable updateFields, WhereFields whereFields, DbTransaction trans)
        {
            if (!entity.QueryOnly)
            {
                Database db = GetDataBase();

                Hashtable new_updateFields = new Hashtable();
                int result = 0;
                string sql = "";
                string whereClause = "";
                string updateClause = "";
                //update clause
                if (null != updateFields && updateFields.Count != 0)
                {
                    StringBuilder sb_updateClause = new StringBuilder();
                    foreach (DictionaryEntry dic in updateFields)
                    {
                        string _key = dic.Key.ToString().ToUpper();
                        //string new_key = "_" + _key;//更新的参数名前添加"_",防止和where条件内的参数名称冲突
                        string new_key =  _key+"_";
                        new_updateFields.Add(new_key, dic.Value);
                        sb_updateClause.Append(string.Format("{0} = {1},", this.GetColumnName(_key), db.CreateParameterName(new_key)));
                    }
                    updateClause = sb_updateClause.ToString(); updateClause = updateClause.Substring(0, updateClause.Length - 1);


                    if (whereFields.Count != 0)
                    {
                        whereClause = CreateAnyWhereClause(whereFields, db);
                    }
                    //where clause
                    if (updateClause != "")
                    {
                        if (whereClause != "")
                        {
                            sql = string.Format("UPDATE {0} SET {1} WHERE {2}", entity.Table, updateClause, whereClause);
                        }
                    }

                    DbCommand comm = db.CreateSqlCommand(sql);

                    CreateEqualsDbParameter(new_updateFields, db, comm);
                    CreateAnyDbParameter(whereFields, db, comm);

                    if (trans == null)
                        result = db.ExecuteNonQuery(comm);
                    else
                        result = db.ExecuteNonQuery(comm, trans);
                }
                return result;
            }
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "Update"));

        }

        public virtual int Update(Hashtable updateFields, WhereFields whereFields)
        {
            return Update(updateFields, whereFields, null);
        }

        public virtual int Update(Hashtable updateFields, Hashtable fields, DbTransaction trans)
        {
            if (!entity.QueryOnly)
            {
                Database db = GetDataBase();

                Hashtable new_updateFields = new Hashtable();
                int result = 0;
                string sql = "";
                string whereClause = "";
                string updateClause = "";
                //update clause
                if (null != updateFields && updateFields.Count != 0)
                {
                    StringBuilder sb_updateClause = new StringBuilder();
                    foreach (DictionaryEntry dic in updateFields)
                    {
                        string _key = dic.Key.ToString().ToUpper();
                        //string new_key = "_" + _key;//更新的参数名前添加"_",防止和where条件内的参数名称冲突
                        string new_key = _key + "_";
                        new_updateFields.Add(new_key, dic.Value);
                        sb_updateClause.Append(string.Format("{0} = {1},", this.GetColumnName(_key), db.CreateParameterName(new_key)));
                    }
                    updateClause = sb_updateClause.ToString(); updateClause = updateClause.Substring(0, updateClause.Length - 1);


                    if (fields.Count != 0)
                    {
                        whereClause = CreateEqualsWhereClause(fields, db);
                    }
                    //where clause
                    if (updateClause != "")
                    {
                        if (whereClause != "")
                        {
                            sql = string.Format("UPDATE {0} SET {1} WHERE {2}", entity.Table, updateClause, whereClause);
                        }
                    }

                    DbCommand comm = db.CreateSqlCommand(sql);

                    CreateEqualsDbParameter(new_updateFields, db, comm);
                    CreateEqualsDbParameter(fields, db, comm);

                    if (trans == null)
                        result = db.ExecuteNonQuery(comm);
                    else
                        result = db.ExecuteNonQuery(comm, trans);
                }
                return result;
            }
            else
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "Update"));
        }

        public virtual int Update(Hashtable updateFields, Hashtable fields)
        {
            return Update(updateFields, fields, null);
        }

        public virtual int Update(Hashtable updateFields, int id)
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(Water.Resources.Globalize.ExceptionResource.ExceptionNotSupportMethod, "GetMaxID"));
            }
            else
            {
                Hashtable ht_fields = new Hashtable();
                ht_fields.Add(entity.PrimaryKeys.ToUpper(), id);
                return Update(updateFields, ht_fields);
            }
        }



        public virtual int Update(T obj, WhereFields whereFields, DbTransaction trans)
        {
            Hashtable values =obj.PropertiesValue ;//obj.FieldsValue;
            return Update(values, whereFields, trans);
        }

        public virtual int Update(T obj, WhereFields whereFields)
        {
            return Update(obj, whereFields, null);
        }

        public virtual int Update(T obj, Hashtable fields, DbTransaction trans)
        {
            Hashtable values = obj.PropertiesValue;
            return Update(values, fields, trans);
        }

        public virtual int Update(T obj, Hashtable fields)
        {
            return Update(obj, fields, null);
        }

        public virtual int Update(T obj, int id)
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "Update"));
            }
            else
            {
                Hashtable ht_fields = new Hashtable();
                ht_fields.Add(entity.PrimaryKeys.ToUpper(), id);
                return Update(obj, ht_fields);
            }

        }


        #region === 辅助方法

        /// <summary>
        /// DataReader转化为 Entity
        /// </summary>
        /// <param name="datareader"></param>
        /// <returns></returns>
        protected virtual T DataReaterToEntity(IDataReader datareader)
        {
            T entity_obj = new T();
            PropertyInfo[] pis = entity_obj.GetType().GetProperties();
            List<string> strFields = entity.AllPropertitiesList;
            foreach (PropertyInfo pi in pis)
            {
                if (strFields.Contains(pi.Name.ToUpper()))
                {
                    object _value=datareader[pi.Name.ToUpper()];
                    if (_value!=DBNull.Value)
                        pi.SetValue(entity_obj,Convert.ChangeType(_value,pi.PropertyType), null);
                }
            }
            return entity_obj;
        }


        private string GetSortExpress(string sortExpress)
        {
            return string.IsNullOrEmpty(sortExpress) ? entity.DefaultOrderByExpress : sortExpress;
        }
        /*
        /// <summary>
        /// 得到查询用的table、view或sql,
        /// 如果有sql就按sql语句查询,若没有sql则看有没有view,有view就按iew查询,没有就按table查询
        /// </summary>
        /// <param name="isSql"></param>
        /// <returns></returns>
        protected virtual string GetTableViewOrSql(out bool isSql)
        {
            if (!string.IsNullOrEmpty(entity.Sql))//sql
            {
                isSql = true;
                return entity.Sql;
            }
            else if (!string.IsNullOrEmpty(entity.View))//view
            {
                isSql = false;
                return entity.View;
            }
            else//table
            {
                isSql = false;
                return entity.Table; ;
            }
        }

        protected virtual string GetBaseQuerySql()
        {
            if (string.IsNullOrEmpty(entity.BaseQuerySql))
            {
                string querySql = string.Empty;
                string from = string.Empty;
                List<string> fieldList = new List<string>();
                foreach (KeyValuePair<string, string> pai in entity.DirectPropertieColumnsMap)
                {
                    fieldList.Add(ALIAS + "." + pai.Value + " AS " + pai.Key);
                }

                //native table or view or sql
                string tableOrViewOrSql = string.Empty;
                if (!string.IsNullOrEmpty(entity.Sql))
                {
                    tableOrViewOrSql = "(" + entity.Sql + ")";
                }
                else if (!string.IsNullOrEmpty(entity.View))
                {
                    tableOrViewOrSql = entity.View;
                }
                else if (!string.IsNullOrEmpty(entity.Table))
                {
                    tableOrViewOrSql = entity.Table;
                }

                from = tableOrViewOrSql + " " + ALIAS;

                int i = 0;
                foreach (PropertyNode refNode in entity.RefProertyNodes)
                {
                    EntityMapping refMap = EntityMappingContainer.Get(refNode.RefEntity);

                    //reference table or view or sql
                    string refTableOrViewOrSql = string.Empty;
                    if (!string.IsNullOrEmpty(refMap.Sql))
                    {
                        refTableOrViewOrSql = "(" + refMap.Sql + ")";
                    }
                    else if (!string.IsNullOrEmpty(refMap.View))
                    {
                        refTableOrViewOrSql = refMap.View;
                    }
                    else if (!string.IsNullOrEmpty(refMap.Table))
                    {
                        refTableOrViewOrSql = refMap.Table;
                    }

                    string refAlias = "R" + i++;
                    string col = entity.PropertieColumnsMap[refNode.Property.ToUpper()];
                    string refCol = refMap.PropertieColumnsMap[refNode.RefProperty.ToUpper()];

                    if (RefMode.CrossJoin != refNode.RefMode)
                    {
                        from = from
                            + " " + RefModeConvert.ToRefModeString(refNode.RefMode) + " "
                            + refTableOrViewOrSql + " " + refAlias
                            + " ON "
                            + ALIAS + "." + col
                            + "="
                            + refAlias + "." + refCol
                            + " ";
                    }
                    else //cross join not on
                    {
                        from = from
                            + " " + RefModeConvert.ToRefModeString(refNode.RefMode) + " "
                            + refTableOrViewOrSql + " " + refAlias
                            + " ";
                    }
                    


                    string[] refDisplayProperties = refNode.RefDisplayProperty.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                    foreach (string pro in refDisplayProperties)
                    {
                        string[] _temp = pro.ToUpper().Split(new string[] { " AS " }, StringSplitOptions.RemoveEmptyEntries);
                        int _count = _temp.Length;
                        if (_count > 0)
                        {
                            string _pro = _temp[0].Trim();
                            string _refCol = refMap.PropertieColumnsMap[_pro];
                            string _as = string.Empty;
                            if (_count > 1)
                                _as = _temp[1].Trim();
                            else
                                _as = _pro;
                            fieldList.Add(refAlias + "." + _refCol + " AS " + _as);
                        }
                    }
                }
                querySql = "SELECT "
                    + string.Join(",", fieldList.ToArray())
                    + " FROM "
                    + from;
                 //querySql;
                EntityMapping entityMapping = EntityMappingContainer.Get(entity.GetType().ToString());
                entityMapping.BaseQuerySql = querySql;
            }

            return entity.BaseQuerySql;
        }
         */
        #endregion



        public virtual List<T> GetAll()
        {
            return GetAll(entity.DefaultOrderByExpress);
        }

        public virtual List<T> GetAll(string sortExpress)
        {
            List<T> li = new List<T>();
            Database db = GetDataBase();
            string sql = db.GetBaseQuerySql(entity);
            if (!string.IsNullOrEmpty(sortExpress))
                sql = sql + " ORDER BY " + sortExpress;
            using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    li.Add(DataReaterToEntity(dr));
                }
            }
            return li;
        }

        public virtual List<T> GetAll(int startRowIndex, int pageSize)
        {
            return GetAll(startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual List<T> GetAll(int startRowIndex, int pageSize, string sortExpress)
        {
            List<T> li = new List<T>();
            string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            //pageSize==-1 时 不分页
            if (pageSize != -1)
                sql = db.CreatePagerSql(query_sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
            else
            {
                if (!string.IsNullOrEmpty(sortExpress))
                    sql = query_sql + " ORDER BY " + sortExpress;
                else
                    sql = query_sql;
            }

            using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    li.Add(DataReaterToEntity(dr));
                }
            }
            return li;

        }



        public virtual DataSet GetAllToDataSet()
        {
            return GetAllToDataSet(entity.DefaultOrderByExpress);
        }

        public virtual DataSet GetAllToDataSet(string sortExpress)
        {
            DataSet ds = new DataSet();
            Database db = GetDataBase();
            string sql = db.GetBaseQuerySql(entity);
            if (!string.IsNullOrEmpty(sortExpress))
                sql = sql + " ORDER BY " + sortExpress;
            ds = db.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        public virtual DataSet GetAllToDataSet(int startRowIndex, int pageSize)
        {
            return GetAllToDataSet(startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual DataSet GetAllToDataSet(int startRowIndex, int pageSize, string sortExpress)
        {
            DataSet ds = new DataSet();
            Database db = GetDataBase();
            string sql = db.GetBaseQuerySql(entity);
            sql = db.CreatePagerSql(sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
            ds = db.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }



        public virtual DbDataReader GetAllToDataReader()
        {
            return GetAllToDataReader(entity.DefaultOrderByExpress);
        }

        public virtual DbDataReader GetAllToDataReader(string sortExpress)
        {
            Database db = GetDataBase();
            string sql = db.GetBaseQuerySql(entity);
            if (!string.IsNullOrEmpty(sortExpress))
                sql = sql + " ORDER BY " + sortExpress;
            return db.ExecuteReader(CommandType.Text, sql);
        }

        public virtual DbDataReader GetAllToDataReader(int startRowIndex, int pageSize)
        {
            return GetAllToDataReader(startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual DbDataReader GetAllToDataReader(int startRowIndex, int pageSize, string sortExpress)
        {
            Database db = GetDataBase();
            string sql = db.GetBaseQuerySql(entity);
            
            //pageSize==-1 时 不分页

            if (pageSize != -1)
                sql = db.CreatePagerSql(sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
            else
            {
                if (!string.IsNullOrEmpty(sortExpress))
                    sql = sql + " ORDER BY " + sortExpress;
            }
            return db.ExecuteReader(CommandType.Text, sql);
        }



        public virtual T FindByID(int id)
        {
            string primaryKays = entity.PrimaryKeys;
            if (primaryKays == "" || string.IsNullOrEmpty(primaryKays) || primaryKays.IndexOf(",") != -1)
            {
                throw new Exception(string.Format(ExceptionResource.ExceptionNotSupportMethod, "FindByID"));
            }
            else
            {
                Hashtable ht_fields = new Hashtable();
                ht_fields.Add(entity.PrimaryKeys.ToUpper(), id);
                return Find(ht_fields)[0];
            }
        }



        public virtual List<T> Find(Hashtable fields)
        {
            return Find(fields, entity.DefaultOrderByExpress);
        }

        public virtual List<T> Find(Hashtable fields, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(),OperatorType.Equals,dic.Value);
            }
            return Find(wheres, sortExpress);
        }

        public virtual List<T> Find(Hashtable fields, int startRowIndex, int pageSize)
        {
            return Find(fields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual List<T> Find(Hashtable fields, int startRowIndex, int pageSize, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(), OperatorType.Equals, dic.Value);
            }
            return Find(wheres, startRowIndex, pageSize, sortExpress);
        }



        public virtual List<T> Find(WhereFields whereFields)
        {
            return Find(whereFields, entity.DefaultOrderByExpress);
        }

        public virtual List<T> Find(WhereFields whereFields, string sortExpress)
        {
            List<T> li = new List<T>();
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql + " WHERE " + where_clause;
            }

            if (!string.IsNullOrEmpty(sortExpress))
                query_sql = query_sql + " ORDER BY " + sortExpress;

            DbCommand comm = db.CreateSqlCommand(query_sql);
            CreateAnyDbParameter(whereFields, db, comm);
            using (IDataReader dr = db.ExecuteReader(comm))
            {
                while (dr.Read())
                {
                    li.Add(DataReaterToEntity(dr));
                }
            }
            return li;
        }

        public virtual List<T> Find(WhereFields whereFields, int startRowIndex, int pageSize)
        {
            return Find(whereFields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual List<T> Find(WhereFields whereFields, int startRowIndex, int pageSize, string sortExpress)
        {
            List<T> li = new List<T>();
            string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql + " WHERE " + where_clause;
            }
            //pageSize==-1 时 不分页
            if (pageSize != -1)
                sql = db.CreatePagerSql(query_sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
            else
            {
                if (!string.IsNullOrEmpty(sortExpress))
                    sql = query_sql + " ORDER BY " + sortExpress;
                else
                    sql = query_sql;
            }
            DbCommand comm = db.CreateSqlCommand(sql);
            CreateAnyDbParameter(whereFields, db, comm);

            using (IDataReader dr = db.ExecuteReader(comm))
            {
                while (dr.Read())
                {
                    li.Add(DataReaterToEntity(dr));
                }
            }
            return li;
        }



        public virtual DataSet FindToDataSet(System.Collections.Hashtable fields)
        {
            return FindToDataSet(fields, entity.DefaultOrderByExpress);
        }

        public virtual DataSet FindToDataSet(System.Collections.Hashtable fields, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(), OperatorType.Equals, dic.Value);
            }
            return FindToDataSet(wheres, sortExpress);
        }

        public virtual DataSet FindToDataSet(System.Collections.Hashtable fields, int startRowIndex, int pageSize)
        {
            return FindToDataSet(fields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual DataSet FindToDataSet(System.Collections.Hashtable fields, int startRowIndex, int pageSize, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(), OperatorType.Equals, dic.Value);
            }
            return FindToDataSet(wheres, startRowIndex, pageSize, sortExpress);
        }



        public virtual DataSet FindToDataSet(WhereFields whereFields)
        {
            return FindToDataSet(whereFields, entity.DefaultOrderByExpress);
        }

        public virtual DataSet FindToDataSet(WhereFields whereFields, string sortExpress)
        {
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql + " WHERE " + where_clause;
            }
            if (!string.IsNullOrEmpty(sortExpress))
                query_sql = query_sql + " ORDER BY " + sortExpress;


            DbCommand comm = db.CreateSqlCommand(query_sql);
            CreateAnyDbParameter(whereFields, db, comm);
            return db.ExecuteDataSet(comm);
        }

        public virtual DataSet FindToDataSet(WhereFields whereFields, int startRowIndex, int pageSize)
        {
            return FindToDataSet(whereFields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual DataSet FindToDataSet(WhereFields whereFields, int startRowIndex, int pageSize, string sortExpress)
        {
                //string query_sql = GetBaseQuerySql();
                string sql;
                Database db = GetDataBase();
                string query_sql = db.GetBaseQuerySql(entity);
                string where_clause = "";
                //string dis_field = isSql ? entity.AllFields : (string.IsNullOrEmpty(entity.View) ? entity.AllFields : entity.AllFields);
                if (null != whereFields && whereFields.Count != 0)//有where条件
                {
                    where_clause = CreateAnyWhereClause(whereFields, db,true);
                    query_sql = query_sql + " WHERE " + where_clause;
                }
               //pageSize==-1 时 不分页
                if (pageSize != -1)
                    sql = db.CreatePagerSql(query_sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
                else
                {
                    if (!string.IsNullOrEmpty(sortExpress))
                        sql = query_sql + " ORDER BY " + sortExpress;
                    else
                        sql = query_sql;
                }

                DbCommand comm = db.CreateSqlCommand(sql);
                CreateAnyDbParameter(whereFields, db, comm);
                return db.ExecuteDataSet(comm);
        }



        public virtual IDataReader FindToDataReader(Hashtable fields)
        {
            return FindToDataReader(fields, entity.DefaultOrderByExpress);
        }

        public virtual IDataReader FindToDataReader(Hashtable fields, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(), OperatorType.Equals, dic.Value);
            }
            return FindToDataReader(wheres, sortExpress);
        }

        public virtual IDataReader FindToDataReader(Hashtable fields, int startRowIndex, int pageSize)
        {
            return FindToDataReader(fields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual IDataReader FindToDataReader(Hashtable fields, int startRowIndex, int pageSize, string sortExpress)
        {
            WhereFields wheres = new WhereFields();
            foreach (DictionaryEntry dic in fields)
            {
                wheres.Add(dic.Key.ToString().ToUpper(), OperatorType.Equals, dic.Value);
            }
            return FindToDataReader(wheres, startRowIndex, pageSize, sortExpress);
        }



        public virtual IDataReader FindToDataReader(WhereFields whereFields)
        {
            return FindToDataReader(whereFields, entity.DefaultOrderByExpress);
        }

        public virtual IDataReader FindToDataReader(WhereFields whereFields, string sortExpress)
        {
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql + " WHERE " + where_clause;
            }
            if (!string.IsNullOrEmpty(sortExpress))
                query_sql = query_sql + " ORDER BY " + sortExpress;


            DbCommand comm = db.CreateSqlCommand(query_sql);
            CreateAnyDbParameter(whereFields, db, comm);
            //(whereFields, db, comm);
            return db.ExecuteReader(comm);
        }

        public virtual IDataReader FindToDataReader(WhereFields whereFields, int startRowIndex, int pageSize)
        {
            return FindToDataReader(whereFields, startRowIndex, pageSize, entity.DefaultOrderByExpress);
        }

        public virtual IDataReader FindToDataReader(WhereFields whereFields, int startRowIndex, int pageSize, string sortExpress)
        {
            string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            //string dis_field = isSql ? entity.AllFields : (string.IsNullOrEmpty(entity.View) ? entity.AllFields : entity.AllFields);
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql + " WHERE " + where_clause;
            }
            sql = db.CreatePagerSql(query_sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress));
            DbCommand comm = db.CreateSqlCommand(sql);
            CreateAnyDbParameter(whereFields, db, comm);
            return db.ExecuteReader(comm);
        }



        public virtual IDataReader FindMatrixToDataReader(WhereFields whereFields, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, DataView matrixColumns)
        {
            return FindMatrixToDataReader(whereFields, entity.DefaultOrderByExpress, matrixColumnField, groupField, valueField, summaryType, matrixColumns);
        }

        public virtual IDataReader FindMatrixToDataReader(WhereFields whereFields, string sortExpress, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, DataView matrixColumns)
        {
            //string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";

            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db, true);
                query_sql = query_sql + " WHERE " + where_clause;
            }

            if (!string.IsNullOrEmpty(sortExpress))
                query_sql = query_sql + " ORDER BY " + sortExpress;

            string matrix_sql = db.CreateMatrixSql(query_sql, matrixColumnField, groupField, valueField, summaryType,matrixColumns);

            DbCommand comm = db.CreateSqlCommand(matrix_sql);
            CreateAnyDbParameter(whereFields, db, comm);
            return db.ExecuteReader(comm);
        }

        public virtual IDataReader FindMatrixToDataReader(WhereFields whereFields, int startRowIndex, int pageSize, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, DataView matrixColumns)
        {
            return FindMatrixToDataReader(whereFields, startRowIndex, pageSize, entity.DefaultOrderByExpress, matrixColumnField, groupField, valueField, summaryType,matrixColumns);
        }

        public virtual IDataReader FindMatrixToDataReader(WhereFields whereFields, int startRowIndex, int pageSize, string sortExpress, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, DataView matrixColumns)
        {
            string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db,true);
                query_sql = query_sql 
                    + " WHERE " 
                    + where_clause;
            }
            /*在视图、内联函数、派生表或子查询的定义中使用 ORDER BY 时，子句只用于确定 TOP 子句返回的行。ORDER BY 不保证在查询这些构造时得到有序结果，除非在查询本身中也指定了 ORDER BY。
             string _sortExpress = this.GetSortExpress(sortExpress);
             if(!string.IsNullOrEmpty(_sortExpress))
                 query_sql += " ORDER BY " + this.GetSortExpress(sortExpress);
             */
            string matrix_sql = db.CreateMatrixSql(query_sql, matrixColumnField, groupField, valueField, summaryType,matrixColumns);
            sql = db.CreatePagerSql(matrix_sql, startRowIndex, pageSize, this.GetSortExpress(sortExpress) /*matrixColumnField*/);
            DbCommand comm = db.CreateSqlCommand(sql);
            CreateAnyDbParameter(whereFields, db, comm);
            return db.ExecuteReader(comm);
        }

        public virtual DataView GetMatrixColumns(WhereFields whereFields, string matrixColumnField, string groupField, string valueField, SummaryType summaryType, string matrixColumnSortExpress)
        {
            List<string> li = new List<string>();
            string sql;
            Database db = GetDataBase();
            string query_sql = db.GetBaseQuerySql(entity);
            string where_clause = "";
            if (null != whereFields && whereFields.Count != 0)//有where条件
            {
                where_clause = CreateAnyWhereClause(whereFields, db, true);
                query_sql = query_sql 
                    + " WHERE " 
                    + where_clause;
            }
            /*
            string _sortExpress = this.GetSortExpress(sortExpress);
            if (!string.IsNullOrEmpty(_sortExpress))
                query_sql += " ORDER BY " + this.GetSortExpress(sortExpress);
            */
            sql = "SELECT " + matrixColumnField + " FROM (" + query_sql + ") C GROUP BY " + matrixColumnField;

            if(!string.IsNullOrEmpty(matrixColumnSortExpress))
                sql = "SELECT * FROM (" + sql + ") O ORDER BY " + matrixColumnSortExpress;

            DbCommand comm = db.CreateSqlCommand(sql);
            CreateAnyDbParameter(whereFields, db, comm);

            return db.ExecuteDataSet(comm).Tables[0].DefaultView;
        }
        #endregion

        #region === 得到自身结构信息
        /// <summary>
        /// 得到自身结构信息(name,column,table,prompt)
        /// </summary>
        /// <returns></returns>
        public virtual DataSet GetSelf()
        {
            DataSet ds = entity.EntityStructure;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                string table=dr["TABLE"].ToString();
                string col=dr["NAME"].ToString();
                dr["PROMPT"] = PromptProxy.GetPromptDesc(table + "." + col);
            }

            return ds;
        }
        public virtual DataSet GetSelf(WhereFields whereFields)
        {
            return GetSelf();
        }
        #endregion

    }
}
